package com.itheima;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itheima.mapper.UserMapper;
import com.itheima.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.*;

/**
 * <p></p>
 *
 * @Description:
 */
@SpringBootTest
public class MPTest2 {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testQueryUesrById() {
        User user = userMapper.selectById(1);
        System.out.println("user = " + user);
    }

    /*
     * 要求：添加名为小明的user数据
     * sql语句：insert into tb_user(xx,xx,xx,...) values (xx,xx,xx,....)
     * */
    @Test
    public void testAddUesrById() {
        User user = User.builder()
                .userName("坤哥")
                .age(18)
                .email("kunkun@163.com")
                .password("123456")
                .build();

        int row = userMapper.insert(user);
        System.out.println("row = " + row);
        System.out.println("user = " + user);

    }

    /*
     * 要求：删除id为 16 的用户
     * sql语句：DELETE FROM tb_user WHERE id=?
     * */
    @Test
    public void testDelUesrById() {
        //16
        int row1 = userMapper.deleteById(16);
        System.out.println("row1 = " + row1);
        //1659031112019718145
        int row2 = userMapper.deleteById(1659031112019718145L);
        System.out.println("row2 = " + row2);

    }

    /*
     * 要求：删除id为 15、14 的用户
     * sql语句：DELETE FROM tb_user WHERE id=? or id=?
     * sql语句：DELETE FROM tb_user WHERE id IN ( ? , ? )
     * */
    @Test
    public void testDelUesrByIds() {
//        List<Long> ids = new ArrayList<>();
//        ids.add(15L);
//        ids.add(14L);

        List<Long> ids = Arrays.asList(15L, 14L);
        int row = userMapper.deleteBatchIds(ids);
        System.out.println("row = " + row);
    }

    /*
     * 要求：删除user_name为王八衰 age 为22 的用户
     * sql语句：DELETE FROM tb_user WHERE user_name = ? AND age = ?
     * */
    @Test
    public void testDelUesrByCondition() {
        //将条件构建到map中
        Map condition = new HashMap();
        condition.put("user_name", "王八衰");
        condition.put("age", 22);

        int row = userMapper.deleteByMap(condition);
        System.out.println("row = " + row);


    }

    /*
     * 修改用户id为 4 的username修改为 李催
     * sql语句：UPDATE tb_user SET user_name=? WHERE id=?
     */
    @Test
    public void testUpdate() {
        User user = User.builder()
                .id(1659036789035311105L)
                .userName("小坤坤")
                .name("蔡坤")
                .age(20)
                .password("123321")
                .email("xiaokun@hotmail.com")
                .build();

        int row = userMapper.updateById(user);
        System.out.println("row = " + row);

    }

    /*
     * 要求：分页查询user数据，每页5条数据,查询第一页数据
     * sql语句：SELECT * FROM tb_user LIMIT 0,5
     * sql语句：SELECT * FROM tb_user LIMIT 5 （简写形式）
     */
    @Test
    public void testPage() {
        //创建Page对象
        Page<User> page = new Page<>(1, 5);
        //Page<User> userPage = userMapper.selectPage(page, null);
        //System.out.println(page == userPage);//true
        userMapper.selectPage(page, null);

        List<User> records = page.getRecords();
        for (User record : records) {
            System.out.println("record = " + record);
        }

        long total = page.getTotal();
        long current = page.getCurrent();
        long size = page.getSize();

        System.out.println("total = " + total);
        System.out.println("current = " + current);
        System.out.println("size = " + size);

    }


    /*
     * 要求：查询用户中姓名包含"伤"，密码为"123456",且年龄为19或者25或者29，查询结果按照年龄降序排序；
     * sql语句：SELECT * FROM tb_user WHERE (user_name LIKE ? AND password = ? AND age IN (?,?,?)) ORDER BY age DESC
     */
    @Test
    public void testConditions1Query() {
        //构建Wrapper
        QueryWrapper<User> queryWrapper =
                new QueryWrapper<User>()// <User> 不能省了
                        .like("user_name", "伤")
                        .eq("password", "123456")
                        .in("age", 19, 25, 29)
                        .orderByDesc("age");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);//遍历打印元素

    }


    /*
     * 要求：查询用户中姓名包含"伤"，密码为"123456",且年龄为19或者25或者29，查询结果按照年龄降序排序；
     * sql语句：SELECT user_name,age FROM tb_user WHERE (user_name LIKE ? AND password = ? AND age IN (?,?,?)) ORDER BY age DESC
     */
    @Test
    public void testConditions2Query() {


    }

    /*
     * 要求：查询用户密码为"123456"的数据，并且要求每页显示5条，查询第二页的数据；
     * sql语句：SELECT user_name,age FROM tb_user WHERE (user_name LIKE ? AND password = ? AND age IN (?,?,?)) ORDER BY age DESC
     */
    @Test
    public void testConditionsPageQuery() {


    }


    /*
     * 要求：查询用户密码为"123456",或者 age 为 20、21、22，并且要求每页显示5条，查询第二页的数据；
     * sql语句：SELECT user_name,age FROM tb_user WHERE  password = ? or age IN (?,?,?)
     */
    @Test
    public void testConditionsOrQuery() {
        Page<User> userPage = new Page<>(2, 5);
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>()
                .eq("password", "123456")
                .or()
                .in("age", 20, 21, 22);

        userMapper.selectPage(userPage, queryWrapper);
        List<User> records = userPage.getRecords();
        records.forEach(System.out::println);
    }

    @Test
    public void testLike() {
        //查包含 伤
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>()
                //.like("user_name", "伤") // %伤%
                //.likeLeft("user_name", "伤")// %伤
                .likeRight("user_name", "伤")// 伤%
                ;

        userMapper.selectList(queryWrapper).forEach(System.out::println);

    }

    //先根据age升序排序，如果年龄相同则按照id降序排序；
    @Test
    public void testOrderBy() {
        //查包含 伤
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>()
                .orderByAsc("age")
                .orderByDesc("id");

        userMapper.selectList(queryWrapper).forEach(System.out::println);

    }

    //先根据age升序排序，如果年龄相同则按照id降序排序,指定用户名，姓名，年龄查询
    @Test
    public void testSelect() {
        //查包含 伤
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>()
                .orderByAsc("age")
                .orderByDesc("id")
                .select("user_name", "name", "age");

        userMapper.selectList(queryWrapper).forEach(System.out::println);

    }


    @Test
    public void testLambdaQuery() {
        //查包含 伤
        /*QueryWrapper<User> queryWrapper = new QueryWrapper<User>()
                .like("user_name", "伤")
                .orderByAsc("age")
                .orderByDesc("id");*/

        LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery();
        lambdaQueryWrapper.like(User::getUserName, "伤")
                .orderByAsc(User::getAge)
                .orderByDesc(User::getId);

        userMapper.selectList(lambdaQueryWrapper).forEach(System.out::println);

    }

    @Test
    public void testLambdaDelete() {
        //删除指定id的数据
        LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery();
        lambdaQueryWrapper.eq(User::getId, 1659071589167202306L);

        int row = userMapper.delete(lambdaQueryWrapper);
        System.out.println("row = " + row);
    }
    @Test
    public void testLambdaUpdate() {
        LambdaUpdateWrapper<User> updateWrapper = Wrappers.lambdaUpdate();
        //条件wrapper
        updateWrapper.eq(User::getUserName, "吴六破")
                .eq(User::getPassword, "123123");

        //封装更新的数据
        User user = User.builder()
                .age(40)
                .build();

        int row = userMapper.update(user, updateWrapper);
        System.out.println("row = " + row);

    }


}
